using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using DTO;
using System.Data;

namespace HeThongDAO
{
    public class KhachHangDAO
    {
        public static DataTable SelectAll()
        {
            string sql = "SELECT * FROM KHACH_HANG";
            return Config.AccessHelper.ExecuteQuery(sql);
        }
        public static DataTable ChonKhachHang(KhachHangDTO kh)
        {
            string sql = "SELECT * FROM KHACH_HANG where MaKhachHang=@makh";
            sql = sql.Replace("@makh", kh.IMaKhachHang.ToString());
            return Config.AccessHelper.ExecuteQuery(sql);
        }
        public static bool Update(DataTable dt)
        {
            try
            {
                Config.AccessHelper.Ghi(dt, "KHACH_HANG");
                return true;
            }
            catch
            {
                return false;
            }
        }

        // Xuan huy
        public static bool CapNhatKhachHang(KhachHangDTO khDTO)
        {
            try
            {
                string sql = "UPDATE KHACH_HANG SET ";
                sql += "HoTen = '" + khDTO.STRTenKhachHang.ToString() + "', ";
                sql += "DiaChi = '" + khDTO.STRDiaChiKhachHang.ToString() + "', ";
                sql += "DienThoai = '" + khDTO.STRDienThoaiKhachHang.ToString() + "', ";
                sql += "Email = '" + khDTO.STREmailKhachHang.ToString() + "', ";
                sql += "NoKhachHang = " + khDTO.STRNoKhachHang.ToString();
                sql += " WHERE MaKhachHang = " + khDTO.IMaKhachHang;

                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static void CapNhat_NoKhachHang(KhachHangDTO kh)
        {
            string sql = "UPDATE KHACH_HANG SET NoKhachHang=@sotien WHERE MaKhachHang=@makh";
            sql = sql.Replace("@sotien", kh.STRNoKhachHang.ToString());
            sql = sql.Replace("@makh", kh.IMaKhachHang.ToString());
            Config.AccessHelper.ExecuteNonQuery(sql);
        }
        public static DataTable LayTongMuaTuThang(KhachHangDTO kh,int thang,int nam)
        {
            string sql = " SELECT hd.MaKhachHang, Sum(cthd.SoLuong*cthd.DonGia) As TongMua " +
                         " FROM CHI_TIET_HOA_DON AS cthd, HOA_DON AS hd " +
                         " WHERE hd.MaHoaDon=cthd.MaHoaDon and datediff('d','@thang/01/@nam',hd.NgayLapHoaDon) >=0 and hd.MaKhachHang=@makh " +
                         " GROUP BY hd.MaKhachHang ";
            sql = sql.Replace("@makh", kh.IMaKhachHang.ToString());
            sql = sql.Replace("@thang", thang.ToString());
            sql = sql.Replace("@nam", nam.ToString());
            return Config.AccessHelper.ExecuteQuery(sql);
        }
        public static DataTable LayTongTraTuThang(KhachHangDTO kh, int thang, int nam)
        {
            string sql = " SELECT pt.MaKhachHang, Sum(pt.SoTienThu) AS TongTra " +
                         " FROM PHIEU_THU AS pt " +
                         " WHERE datediff('d','@thang/01/@nam',pt.NgayThu) >=0 and pt.MaKhachHang=@makh " +
                         " GROUP BY pt.MaKhachHang ";
            sql = sql.Replace("@makh", kh.IMaKhachHang.ToString());
            sql = sql.Replace("@thang", thang.ToString());
            sql = sql.Replace("@nam", nam.ToString());
            return Config.AccessHelper.ExecuteQuery(sql);
        }
        public static DataTable LayTongMuaTrongThang(KhachHangDTO kh, int thang, int nam)
        {
            string sql = " SELECT hd.MaKhachHang, Sum(cthd.SoLuong*cthd.DonGia) As TongMua " +
                         " FROM CHI_TIET_HOA_DON AS cthd, HOA_DON AS hd " +
                         " WHERE hd.MaHoaDon=cthd.MaHoaDon and month(hd.NgayLapHoaDon)=@thang and year(hd.NgayLapHoaDon)=@nam and hd.MaKhachHang=@makh " +
                         " GROUP BY hd.MaKhachHang ";
            sql = sql.Replace("@makh", kh.IMaKhachHang.ToString());
            sql = sql.Replace("@thang", thang.ToString());
            sql = sql.Replace("@nam", nam.ToString());
            return Config.AccessHelper.ExecuteQuery(sql);
        }
        public static DataTable LayTongTraTrongThang(KhachHangDTO kh, int thang, int nam)
        {
            string sql = " SELECT pt.MaKhachHang, Sum(pt.SoTienThu) AS TongTra " +
                         " FROM PHIEU_THU AS pt " +
                         " WHERE month(pt.NgayThu)=@thang and year(pt.NgayThu)=@nam and pt.MaKhachHang=@makh " +
                         " GROUP BY pt.MaKhachHang ";
            sql = sql.Replace("@makh", kh.IMaKhachHang.ToString());
            sql = sql.Replace("@thang", thang.ToString());
            sql = sql.Replace("@nam", nam.ToString());
            return Config.AccessHelper.ExecuteQuery(sql);
        }
    }
}
